﻿using Dapper;
using GLstudent.Models;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace GLstudent.DAL
{
    public class DAL_teacher
    {
        /// <summary>
        /// 按教师编号查询本班学生信息
        /// </summary>
        /// <param name="tbianhao">教师编号</param>
        /// <returns></returns>
        public List<xueshengshitu> chaxunid(string tbianhao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.Query<xueshengshitu>("SELECT student.id AS sid,student.s_name AS sname,student.s_sex AS ssex,student.s_xuehao AS sxuehao,student.b_name AS bname,student.s_lianxi AS slianxi, student.s_dizhi AS sdizhi,student.s_shenfenzheng AS sshenfenzheng,student.s_minzu AS sminzu,student.s_age AS sage FROM teacher JOIN banji ON teacher.t_bianhao = teacher.t_bianhao JOIN student ON banji.b_name = student.b_name WHERE banji.t_bianhao = @tbianhao GROUP BY sid,sname,ssex,sxuehao,bname,slianxi,sdizhi,sshenfenzheng,sminzu,sage ORDER BY sid ", new { tbianhao }).ToList();
            return chaxunid;
        }
        /// <summary>
        /// 按教师编号模糊查询本班学生
        /// </summary>
        /// <param name="mohu">模糊查询输入的字符</param>
        /// <param name="tbianhao">教师编号</param>
        /// <returns></returns>
        public List<xueshengshitu> mohuxuesheng(string mohu, string tbianhao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<xueshengshitu>
                ($"SELECT student.id AS sid,student.s_name AS sname,student.s_sex AS ssex,student.s_xuehao AS sxuehao,student.b_name AS bname,student.s_lianxi AS slianxi, student.s_dizhi AS sdizhi,student.s_shenfenzheng AS sshenfenzheng,student.s_minzu AS sminzu,student.s_age AS sage FROM teacher JOIN banji ON teacher.t_bianhao = teacher.t_bianhao JOIN student ON banji.b_name = student.b_name WHERE banji.t_bianhao = @tbianhao AND student.s_name LIKE  '%{mohu}%' GROUP BY sid,sname,ssex,sxuehao,bname,slianxi,sdizhi,sshenfenzheng,sminzu,sage ORDER BY sid  ", new { mohu, tbianhao }).ToList();
            return list;
        }
        /// <summary>
        /// 按ID查询学生信息
        /// </summary>
        /// <param name="id">要查询的id</param>
        /// <returns></returns>
        public student chaxunxueshengid(int id)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.QueryFirstOrDefault<student>("SELECT * FROM student WHERE id = @id  ", new { id });
            return chaxunid;
        }
        /// <summary>
        /// 更改学生信息的方法
        /// </summary>
        /// <param name="g">与student类对应的参数</param>
        /// <returns></returns>
        public int Updatexuesheng(student g)
        {
            var sql = "UPDATE student set s_name=@s_name, s_sex=@s_sex, s_xuehao=@s_xuehao, b_name=@b_name, s_lianxi=@s_lianxi, s_dizhi=@s_dizhi, s_shenfenzheng=@s_shenfenzheng, s_minzu=@s_minzu, s_age=@s_age WHERE id= @id";
            MySqlConnection mySqlConnection = new MySqlConnection
                (ConfigHelper.Configuration["MySQLConnectionString"]);
            var genggai = mySqlConnection.Execute(sql, g);
            return genggai;
        }
        /// <summary>
        /// 删除学生信息的方法
        /// </summary>
        /// <param name="id">要删除id</param>
        /// <returns></returns>
        public int Deletedxuesheng(int id)
        {
            var sql = "DELETE FROM student WHERE id = @id";
            MySqlConnection mySqlConnection = new MySqlConnection
                (ConfigHelper.Configuration["MySQLConnectionString"]);
            var shanchu = mySqlConnection.Execute(sql, new { id });
            return shanchu;
        }
        /// <summary>
        /// 增加学生信息的方法
        /// </summary>
        /// <param name="zengjia">与student类对应的参数</param>
        /// <returns></returns>
        public int Insertxuesheng(student zengjia)
        {
            var sql = "insert into student (s_name,s_sex,s_xuehao,b_name,s_lianxi,s_dizhi,s_shenfenzheng,s_minzu,s_age) values (@s_name,@s_sex,@s_xuehao,@b_name,@s_lianxi,@s_dizhi,@s_shenfenzheng,@s_minzu,@s_age)";
            MySqlConnection mySqlConnection = new MySqlConnection
            (ConfigHelper.Configuration["MySQLConnectionString"]);
            var zengjia1 = mySqlConnection.Execute(sql, zengjia);
            return zengjia1;
        }
        /// <summary>
        /// 按教师编号查询本班学生成绩
        /// </summary>
        /// <param name="tbianhao">教师编号</param>
        /// <returns></returns>
        public List<chengjitongjitu> chaxuntongji(string tbianhao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.Query<chengjitongjitu>("SELECT chengji.id AS cid,student.s_name AS sname,student.s_xuehao AS xuehao,chengji.fenshu AS fenshu,kecheng.k_name AS kname FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao  JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE banji.t_bianhao = @tbianhao GROUP BY cid,sname,xuehao,fenshu,kname ORDER BY cid ", new { tbianhao }).ToList();
            return chaxunid;
        }
        /// <summary>
        /// 按教师编号模糊查询本班学生成绩
        /// </summary>
        /// <param name="mohu">模糊查询输入的字符</param>
        /// <param name="tbianhao">教师编号</param>
        /// <returns></returns>
        public List<chengjitongjitu> mohubenbanchengji(string mohu, string tbianhao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<chengjitongjitu>
                ($"SELECT chengji.id AS cid,student.s_name AS sname,student.s_xuehao AS xuehao,chengji.fenshu AS fenshu,kecheng.k_name AS kname FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao  JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE banji.t_bianhao = @tbianhao AND student.s_name LIKE  '%{mohu}%' GROUP BY cid,sname,xuehao,fenshu,kname ORDER BY cid  ", new { mohu, tbianhao }).ToList();
            return list;
        }
        /// <summary>
        /// 按教师编号查询所任课程学生成绩
        /// </summary>
        /// <param name="tbianhao">教师编号</param>
        /// <returns></returns>
        public List<chengjitongjitu> chaxunkechengchengji(string tbianhao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.Query<chengjitongjitu>(" SELECT chengji.id AS cid,student.s_name AS sname,student.s_xuehao AS xuehao,chengji.fenshu AS fenshu,kecheng.k_name AS kname FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE kecheng.t_bianhao = @tbianhao GROUP BY cid,sname,xuehao,fenshu,kname ORDER BY cid ", new { tbianhao }).ToList();
            return chaxunid;
        }
        /// <summary>
        /// 按教师编号模糊查询本班学生成绩
        /// </summary>
        /// <param name="mohu">模糊查询输入的字符</param>
        /// <param name="tbianhao">教师编号</param>
        /// <returns></returns>
        public List<chengjitongjitu> mohukechengchengji(string mohu, string tbianhao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<chengjitongjitu>
                ($"SELECT chengji.id AS cid,student.s_name AS sname,student.s_xuehao AS xuehao,chengji.fenshu AS fenshu,kecheng.k_name AS kname FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao  JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE kecheng.t_bianhao = @tbianhao AND student.s_name LIKE  '%{mohu}%' GROUP BY cid,sname,xuehao,fenshu,kname ORDER BY cid  ", new { mohu, tbianhao }).ToList();
            return list;
        }
        /// <summary>
        /// 按教师编号查询本班学生成绩，并按学生姓名排序
        /// </summary>
        /// <param name="tbianhao">教师编号</param>
        /// <returns></returns>
        public List<chengjitongjitu> chaxunpaixu(string tbianhao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.Query<chengjitongjitu>("SELECT chengji.id AS cid,student.s_name AS sname,student.s_xuehao AS xuehao,chengji.fenshu AS fenshu,kecheng.k_name AS kname FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao  JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE banji.t_bianhao = @tbianhao GROUP BY cid,sname,xuehao,fenshu,kname ORDER BY sname ", new { tbianhao }).ToList();
            return chaxunid;
        }
        /// <summary>
        /// 按教师编号查询所任课程学生成绩,并按学生成绩排序
        /// </summary>
        /// <param name="tbianhao">教师编号</param>
        /// <returns></returns>
        public List<chengjitongjitu> chaxunkechengchengjipaixu(string tbianhao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.Query<chengjitongjitu>(" SELECT chengji.id AS cid,student.s_name AS sname,student.s_xuehao AS xuehao,chengji.fenshu AS fenshu,kecheng.k_name AS kname FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE kecheng.t_bianhao = @tbianhao GROUP BY cid,sname,xuehao,fenshu,kname ORDER BY fenshu DESC ", new { tbianhao }).ToList();
            return chaxunid;
        }
        /// <summary>
        /// 更改学生成绩信息的方法
        /// </summary>
        /// <param name="g">与chengji类对应的参数</param>
        /// <returns></returns>
        public int Updatexueshengchengji(chengji g)
        {
            var sql = "UPDATE chengji set kecheng_id=@kecheng_id, t_bianhao=@t_bianhao, fenshu=@fenshu, s_xuehao=@s_xuehao WHERE id= @id";
            MySqlConnection mySqlConnection = new MySqlConnection
                (ConfigHelper.Configuration["MySQLConnectionString"]);
            var genggai = mySqlConnection.Execute(sql, g);
            return genggai;
        }
        /// <summary>
        /// 按ID查询学生成绩信息
        /// </summary>
        /// <param name="id">要查询的id</param>
        /// <returns></returns>
        public chengji chaxunxueshengchengjiid(int id)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.QueryFirstOrDefault<chengji>("SELECT * FROM chengji WHERE id = @id  ", new { id });
            return chaxunid;
        }
        /// <summary>
        /// 按ID查询课程信息
        /// </summary>
        /// <param name="id">要查询的id</param>
        /// <returns></returns>
        public kecheng chaxunkechengid(int id)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.QueryFirstOrDefault<kecheng>("SELECT * FROM kecheng WHERE id = @id  ", new { id });
            return chaxunid;
        }
        /// <summary>
        /// 删除学生成绩信息的方法
        /// </summary>
        /// <param name="id">要删除的id</param>
        /// <returns></returns>
        public int Deletedchengji(int id)
        {
            var sql = "DELETE FROM chengji WHERE id = @id";
            MySqlConnection mySqlConnection = new MySqlConnection
                (ConfigHelper.Configuration["MySQLConnectionString"]);
            var shanchu = mySqlConnection.Execute(sql, new { id });
            return shanchu;
        }
        /// <summary>
        /// 增加学生分数的方法
        /// </summary>
        /// <param name="zengjia">与chengji类对应的参数</param>
        /// <returns></returns>
        public int Insertfenshu(chengji zengjia)
        {
            var sql = "insert into chengji (kecheng_id,t_bianhao,fenshu,s_xuehao) values (@kecheng_id,@t_bianhao,@fenshu,@s_xuehao)";
            MySqlConnection mySqlConnection = new MySqlConnection
            (ConfigHelper.Configuration["MySQLConnectionString"]);
            var zengjia1 = mySqlConnection.Execute(sql, zengjia);
            return zengjia1;
        }
        /// <summary>
        /// 查询课程视图的所有信息
        /// </summary>
        /// <returns></returns>
        public List<chakankecheng> chaxunkechengsuoyou()
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.Query<chakankecheng>("SELECT kecheng.id AS kechengid,kecheng.k_name AS kcname,kecheng.t_bianhao AS renkejiaoshi,teacher.t_name AS jiaoshiname FROM kecheng JOIN teacher ON kecheng.t_bianhao = teacher.t_bianhao ").ToList();
            return chaxunid;
        }
        /// <summary>
        /// 模糊查询课程视图的所有信息
        /// </summary>
        /// <param name="mohu">模糊查询输入的字符</param>
        /// <returns></returns>
        public List<chakankecheng> mohukechengsuoyou(string mohu)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var list = mySqlConnection.Query<chakankecheng>
                ($"SELECT kecheng.id AS kechengid,kecheng.k_name AS kcname,kecheng.t_bianhao AS renkejiaoshi,teacher.t_name AS jiaoshiname FROM kecheng JOIN teacher ON kecheng.t_bianhao = teacher.t_bianhao WHERE kecheng.k_name LIKE  '%{mohu}%' ", new { mohu }).ToList();
            return list;
        }
        /// <summary>
        /// 按教师编号查询本班学生各科成绩平均分
        /// </summary>
        /// <param name="bianhao">教师编号</param>
        /// <returns></returns>
        public List<gekepingjunfen> chaxunpingjunfen(string bianhao)
        {
            MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString"]);
            var chaxunid = mySqlConnection.Query<gekepingjunfen>("SELECT (SELECT round(AVG(fenshu),2) FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE chengji.kecheng_id = 1 AND banji.t_bianhao = @bianhao) AS BS,(SELECT round(AVG(fenshu),2) FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE chengji.kecheng_id = 2 AND banji.t_bianhao = @bianhao) AS WEB,(SELECT round(AVG(fenshu),2) FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE chengji.kecheng_id = 3 AND banji.t_bianhao = @bianhao) AS Linux,(SELECT round(AVG(fenshu),2) FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE chengji.kecheng_id = 4 AND banji.t_bianhao = @bianhao) AS Vue,(SELECT round(AVG(fenshu),2) FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE chengji.kecheng_id = 5 AND banji.t_bianhao = @bianhao) AS MySql,(SELECT round(AVG(fenshu),2) FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE chengji.kecheng_id = 6 AND banji.t_bianhao = @bianhao) AS element,(SELECT round(AVG(fenshu),2) FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao  JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE chengji.kecheng_id = 7 AND banji.t_bianhao = @bianhao) AS yingyu,(SELECT round(AVG(fenshu),2) FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE chengji.kecheng_id = 8 AND banji.t_bianhao = @bianhao) AS java,(SELECT round(AVG(fenshu),2) FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE chengji.kecheng_id = 9 AND banji.t_bianhao = @bianhao) AS PHP,(SELECT round(AVG(fenshu),2) FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao  JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE chengji.kecheng_id = 10 AND banji.t_bianhao = @bianhao) AS C,(SELECT round(AVG(fenshu),2) FROM chengji JOIN teacher ON chengji.t_bianhao = chengji.t_bianhao JOIN student ON chengji.s_xuehao = student.s_xuehao  JOIN kecheng ON chengji.kecheng_id = kecheng.id JOIN banji ON student.b_name = banji.b_name WHERE chengji.kecheng_id = 11 AND banji.t_bianhao = @bianhao) AS JavaScript FROM chengji GROUP BY BS,WEB,Linux,Vue,MySql,element,yingyu,java,PHP,C,JavaScript ", new { bianhao }).ToList();
            return chaxunid;
        }
    }
}
